package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.User;

public class UserDao {
	Connection conn = null;
	PreparedStatement pstm=null;
	ResultSet rs = null;
	int pageCount;
	int rowCount;
	int pageSize=5;
	public boolean  queryUser(User user) {
		boolean flag=false;
		try {
			conn=new DBConn().getConn();
			String sql="select count(*)  from user where username=? and password=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, user.getUsername());
			pstm.setString(2,user.getPassword());
			
			rs=pstm.executeQuery();
			if(rs.next()) {
				int  count=rs.getInt(1);
				if(count>0)
				  flag=true;
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return flag;
	}
	public User queryById(int id) {
		User user=null;
		try {
			conn=new DBConn().getConn();
			String sql="select * from user where id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
			rs=pstm.executeQuery();
			if(rs.next()) {
				user=new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return user;
	}
	public int getPageCount() {   
		try {
			conn = new DBConn().getConn();
			String sql="select count(*) from user";
			pstm=conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			if (rs.next()) {
				rowCount = rs.getInt(1);
			}
			if (rowCount % pageSize == 0) {
				pageCount = rowCount / pageSize;
			} else {
				pageCount = rowCount / pageSize + 1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return pageCount;
	}
	
	public List<User> queryUserByid(int id){
		User user=null;
		List<User> list=new ArrayList<>();
		try {
			conn=new DBConn().getConn();
			String sql="select * from user where id=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, id);
			rs=pstm.executeQuery();
			while(rs.next()) {
				user=new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				list.add(user);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return list;
	}
	
	public List<User> queryAllUser(){
		User user=null;
		List<User> list=new ArrayList<>();
		try {
			conn=new DBConn().getConn();
			String sql="select * from user ";
			pstm=conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			while(rs.next()) {
				user=new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				list.add(user);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return list;
	}
	
	public boolean addUser(User user) {
		boolean flag = false;
		try {
			conn = new DBConn().getConn();
			String sql = "insert into user(username,passwd) values(?,?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, user.getUsername());
			pstm.setString(2, user.getPassword());
			int count = pstm.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return flag;
	}
	
	public boolean updateUserById(int id,User user) {
		boolean flag = false;
		try {
			conn = new DBConn().getConn();
			String sql = "update  user set username=?,passwd=? where id=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setString(1, user.getUsername());
			pstm.setString(2, user.getPassword());
			pstm.setInt(3, id);
			int count = pstm.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return flag;
	}
	
	public boolean  deleteUserById(int id) {
		boolean flag = false;
		try {
			conn = new DBConn().getConn();
			String sql = "delete from  user  where id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, id);
				
			int count = pstm.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return flag;
	}
	
	public void close() {
		try {
			if(rs!=null) rs.close();
			if(pstm!=null)  pstm.close();
			if(conn!=null)  conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
